﻿using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SQLite;
using Dapper;
using System.Data;
namespace SqlBuilder.Code
{
    public class SQLiteHelper
    {
        //private string DBPath = Path.Combine(Environment.CurrentDirectory, "sqlbuilderdb");

        private string _DBName = string.Empty;
        private string _DBPwd = string.Empty;
        private string _DBPath = string.Empty;
        private string _DBFileName = string.Empty;
        private static SQLiteHelper _Current = null;
        private string _CreatTableSql = string.Empty;

        public string DBName { get { return this._DBName; } }

        public string DBPwd { get { return this._DBPwd; } }

        public string DBPath { get { return this._DBPath; } }

        public string DBFileName { get { return this.DBFileName; } }

        public static SQLiteHelper Current
        {
            get
            {
                return _Current;
            }
        }

        public SQLiteHelper()
        {
            this._DBName = "sqlbuilder.db";
            this._DBPwd = "5201314wll";
            this._DBPath = Environment.CurrentDirectory;
            this._DBFileName = Path.Combine(Environment.CurrentDirectory, _DBName);
            _Current = this;
            StringBuilder sb = new StringBuilder();
            sb.Append("CREATE TABLE IF NOT EXISTS sqltemplate(id TEXT PRIMARY KEY NOT NULL, name TEXT NOT NULL, template TEXT NOT NULL);");
            sb.Append("CREATE TABLE IF NOT EXISTS sqlmap(id TEXT PRIMARY KEY NOT NULL, templateId TEXT NOT NULL,template text not null,excelIndex text not null,script text not null);");
            _CreatTableSql = sb.ToString();
        }



        public bool InitDB()
        {
            if (!IsHasDB())
            {
                CreatDB();
            }


            CreatTable();
            return true;

        }

        public bool IsHasDB()
        {
            return File.Exists(this._DBFileName);
        }


        public void CreatDB()
        {
            SQLiteConnection.CreateFile(this._DBFileName);
            //连接数据库  
            SQLiteConnection conn = (SQLiteConnection)GetConn();
            conn.ChangePassword(this._DBPwd);
        }

        public void CreatTable()
        {
            IDbConnection conn = GetConn();
            conn.Execute(this._CreatTableSql);
        }

        public IDbConnection GetConn()
        {
            SQLiteConnection conn = new SQLiteConnection();
            SQLiteConnectionStringBuilder connstr = new SQLiteConnectionStringBuilder();
            connstr.DataSource = this._DBFileName;
            connstr.Password = this._DBPwd;//设置密码，SQLite ADO.NET实现了数据库密码保护  
            conn.ConnectionString = connstr.ToString();
            conn.Open();

            return conn;
        }
    }
}
